package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.model.PsPointN;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface PsPointMapper extends BaseMapper<PsPointN> {

    // 查询某个坐标在范围内最近的点
    @Select("SELECT survey_no,pointdepth,ST_AsText(geom) as geom FROM ps_point_n WHERE ST_DWithin(geom, ST_MakePoint(#{X},#{Y}), #{distance}) ORDER BY ST_Distance(geom, ST_MakePoint(#{X},#{Y})) LIMIT 1;")
    List<PsPointN> selectByPoint(@Param("X") Double coorX, @Param("Y") Double coorY, @Param("distance") Integer distance1);

    // 查询某个extent的数据
    @Select("SELECT survey_no,pointdepth,ST_AsText(geom) as geom,coor_recor,zone_code,evcode_mai FROM ps_point_n WHERE ST_Intersects(geom, ST_MakeEnvelope(#{minX}, #{minY}, #{maxX}, #{maxY}));")
    List<PsPointN> selectByExtend(@Param("minX") Double minX, @Param("minY") Double minY, @Param("maxX") Double maxX, @Param("maxY") Double maxy);

    // 查询某个wkt的数据
    @Select("SELECT survey_no,pointdepth,ST_AsText(geom) as geom,coor_recor,zone_code,evcode_mai FROM ps_point_n WHERE ST_Intersects(geom, ST_GeomFromText(#{wkt}, 0)) LIMIT 100 OFFSET #{page};")
    List<PsPointN> selectByPolygon(@Param("wkt") String wkt,@Param("page") Integer page);

    // 查询某个wkt的数据数量
    @Select("SELECT COUNT(*) FROM ps_point_n WHERE ST_Intersects(geom, ST_GeomFromText(#{wkt}, 0));")
    Integer getCountByPolygon(@Param("wkt") String wkt);

}
